/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package qs.crm.modules.client.cstCustomer.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import qs.crm.modules.client.cstCustomer.domain.CstCustomer;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
* @author tong
* @date 2023-05-31
**/
public interface CstCustomerRepository extends JpaRepository<CstCustomer, Long>, JpaSpecificationExecutor<CstCustomer> {
    @Query(value = "SELECT cust_no,cust_name,cust_region,cust_manager_id,cust_manager_name,cust_level_label,cust_satisfy,cust_credit,cust_addr,cust_zip,cust_tel,cust_fax,cust_website,cust_licence_no,cust_chieftain,cust_bankroll,cust_turnover,cust_bank,cust_bank_account,cust_local_tax_no,cust_national_tax_no,cust_status,odr_date FROM (\n" +
            "SELECT cust_no,cust_name,cust_region,cust_manager_id,cust_manager_name,cust_level_label,cust_satisfy,cust_credit,cust_addr,cust_zip,cust_tel,cust_fax,cust_website,cust_licence_no,cust_chieftain,cust_bankroll,cust_turnover,cust_bank,cust_bank_account,cust_local_tax_no,cust_national_tax_no,cust_status,jidu,odr_date FROM cst_customer a LEFT JOIN (select orders.odr_customer,odr_date,  TIMESTAMPDIFF(QUARTER,MAX(odr_date),CURDATE()) AS jidu   from orders group by odr_customer) b  ON a.cust_name=b.odr_customer  HAVING IFNULL(jidu,12) >= 2 \n" +
            ") m" ,nativeQuery = true)
    public List<Map<String,Object>> findCustNo();

    @Query(value = "SELECT cust_no,cust_name,cust_region,cust_manager_id,cust_manager_name,cust_level_label,cust_satisfy,cust_credit,cust_addr,cust_zip,cust_tel,cust_fax,cust_website,cust_licence_no,cust_chieftain,cust_bankroll,cust_turnover,cust_bank,cust_bank_account,cust_local_tax_no,cust_national_tax_no,cust_status,odr_date FROM (\n" +
            "SELECT cust_no,cust_name,cust_region,cust_manager_id,cust_manager_name,cust_level_label,cust_satisfy,cust_credit,cust_addr,cust_zip,cust_tel,cust_fax,cust_website,cust_licence_no,cust_chieftain,cust_bankroll,cust_turnover,cust_bank,cust_bank_account,cust_local_tax_no,cust_national_tax_no,cust_status,jidu,odr_date FROM cst_customer a LEFT JOIN (select orders.odr_customer,odr_date,  TIMESTAMPDIFF(QUARTER,MAX(odr_date),CURDATE()) AS jidu   from orders group by odr_customer) b  ON a.cust_name=b.odr_customer  HAVING IFNULL(jidu,12) >= 2 \n" +
            ") m" ,nativeQuery = true)
    public List<CstCustomer> updatCstCustomer();

    @Query(value = "SELECT cust_no,cust_name,cust_region,cust_manager_id,cust_manager_name,cust_level_label,cust_satisfy,cust_credit,cust_addr,cust_zip,cust_tel,cust_fax,cust_website,cust_licence_no,cust_chieftain,cust_bankroll,cust_turnover,cust_bank,cust_bank_account,cust_local_tax_no,cust_national_tax_no,cust_status FROM cst_customer WHERE cust_name = ?1", nativeQuery = true)
    public CstCustomer byName(String custName);

    @Query(value = "SELECT cust_name AS name,IFNULL(pric1,0.00) AS sumpric \n" +
            "FROM cst_customer a \n" +
            "LEFT JOIN \n" +
            "(SELECT odr_customer,pric1 FROM \n" +
            "(SELECT *,SUM(pric) AS pric1  FROM orders AS d  \n" +
            "LEFT JOIN \n" +
            "(SELECT odd_order_id,SUM(odd_price*odd_count) AS pric FROM orders_line GROUP BY odd_order_id) c \n" +
            "ON d.odr_id=c.odd_order_id GROUP BY odr_customer) e ) f \n" +
            "ON a.cust_name=f.odr_customer\n",nativeQuery = true)
    public List<Map<String, BigDecimal>> gongxian();

    @Query(value = "SELECT a.`value`,a.label,IFNULL(b.count,0) AS 'count' FROM (SELECT `value`, label FROM sys_dict_detail WHERE dict_id = 11) a LEFT JOIN (SELECT c.cust_name,count(*) AS count, c.cust_level_label AS value FROM cst_customer c GROUP BY c.cust_level_label) b ON a.`value`=b.`value`",nativeQuery = true)
    public List<Map<String, Object>> gongchen();

    @Query(value = "SELECT label,IFNULL(`value`,0) AS `value`,svr_type AS type, IFNULL(count,0) AS count FROM (SELECT label,`value` FROM sys_dict_detail WHERE dict_id=(SELECT dict_id FROM sys_dict WHERE name = 'svr_type')) a LEFT JOIN (SELECT svr_type,count(*) count FROM sct_service GROUP BY svr_type) b ON a.`value`=svr_type",nativeQuery = true)
    public List<Map<String, Object>> fenxi();

    @Query(value = "SELECT lst_lost_date AS date,lst_cust_name AS `name`,lst_cust_manager_name AS chjlNname,lst_reason AS lstReason FROM cst_lost WHERE lst_lost_date IS NOT NULL AND lst_status=(SELECT `value` FROM sys_dict_detail WHERE dict_id = (SELECT dict_id FROM sys_dict WHERE description = '客户状态') AND label = '流失')",nativeQuery = true)
    public List<Map<String,Object>> liushifenxi();
}